package com.mysql.mybatis.service.impl;

import com.mysql.mybatis.dao.model.User;
import com.mysql.mybatis.service.JdbcTemplateService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

/**
 * 描述: JdbcTemplate 测试
 *
 * @author : lhb
 * @date : 2019-11-27 10:48
 */
@Service
public class JdbcTemplateImpl implements JdbcTemplateService {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public void save(User user) {
        String sql = "insert into user(id,user_name,description,is_deleted) values(SEQ_TEST.nextval,?,?,?)";
        jdbcTemplate.update(sql,user.getUserName(), user.getDescription(), user.getIsDeleted());
    }

    @Override
    public User getById(Long id) {
        String sql = "select * from user where id = ?";
        return jdbcTemplate.query(sql, new UserRowMapper(), id).get(0);
    }

    @Override
    public void update(User user) {
        String sql = "update user set user_name=?,description=?,is_deleted=? where id=?";
        jdbcTemplate.update(sql, user.getUserName(), user.getDescription(), user.getIsDeleted(), user.getId());
    }

    @Override
    public void deleteById(Long id) {
        String sql = "delete from user where id = ?";
        jdbcTemplate.update(sql, id);
    }

    @Override
    public List<User> getAll() {
        String sql = "select * from user";
        return jdbcTemplate.query(sql, new UserRowMapper());
    }

    @Override
    public Long getSeq() {
        String sql = "select * from SEQ_CUSTOMER.nextVal";
        return (Long) jdbcTemplate.query(sql, new RowMapper() {
            @Override
            public Object mapRow(ResultSet resultSet, int i) throws SQLException {
                return resultSet.getLong(1);
            }
        }).get(0);
    }

    class UserRowMapper implements RowMapper<User> {

        @Override
        public User mapRow(ResultSet rs, int rowNum) throws SQLException {
            return new UserExtractor().extractData(rs);
        }
    }

    /**
     * 获取结果集ResultSet来获取想要的字段
     */
    class UserExtractor implements ResultSetExtractor<User> {

        @Override
        public User extractData(ResultSet rs) throws SQLException, DataAccessException {
            User user = new User();
            user.setId(rs.getLong("id"));
            user.setUserName(rs.getString("user_name"));
            user.setDescription(rs.getString("description"));
            user.setIsDeleted(rs.getInt("is_deleted"));
            return user;
        }
    }

}
